﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using RN.ERP.Web.EnterpriseService;
using RN.ERP.Web.ModelService;
using Newtonsoft.Json;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Text.RegularExpressions;
using System.Configuration;
using Telerik.Web.UI;

namespace RN.ERP.Web.Enterprise
{
    public partial class ImportEnterpriseInfoByExcel : System.Web.UI.Page
    {
        /// <summary>
        /// 存放上传excel的文件夹
        /// </summary>
        private const string EXCEL_FOLDER_NAME = "../Download/excel";

        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            string fileName = this.fileUpload.PostedFile.FileName;
            string path = Server.MapPath(EXCEL_FOLDER_NAME);
            FileInfo finfo = new FileInfo(fileName);
            string ext = finfo.Extension;

            if (!".xls|.xlsx".Contains(ext))
            {
                //Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('只能上传excel格式的文件！');</script>");
                RadWindowManager1.RadAlert("只能上传excel格式的文件！", 300, 150, "提示", "");
                return;
            }

            fileName = DateTime.Now.ToString("yyyyMMdd") + Guid.NewGuid() + ext;
            string filePath = path + "/" + fileName;
            this.fileUpload.PostedFile.SaveAs(filePath);

            ImportCompanyInfoByExcel(filePath);

        }

        /// <summary>
        /// 将企业信息excel中的数据导入数据库
        /// </summary>
        /// <param name="filePath"></param>
        private void ImportCompanyInfoByExcel(string filePath)
        {
            string msg = string.Empty;

            

            List<ImportCompanyInfo> failList = null;
            List<ImportCompanyInfo> list = GetCompanyInfoByExcel(filePath, out msg, out failList);

            using (EnterpriseServiceClient client = new EnterpriseServiceClient())
            {
                try
                {
                    string corpStr = JsonConvert.SerializeObject(list);
                    string repeatCorpStr = client.BatchImportCorpUser(corpStr, ConfigurationManager.AppSettings["sysArea"]);
                    if (!string.IsNullOrEmpty(repeatCorpStr))
                    {
                        List<ImportCompanyInfo> repeatList = JsonConvert.DeserializeObject<List<ImportCompanyInfo>>(repeatCorpStr);
                        failList.AddRange(repeatList);
                        
                        liCorp.Style["Display"] = "Block";
                        ListCorps.DataSource = failList;
                        ListCorps.DataBind();
                    }   
                    msg = "导入成功！";
                }
                catch (Exception e)
                {
                    msg = e.Message;
                }
            }
            //Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('" + msg + "')</script>");
            RadWindowManager1.RadAlert(msg, 300, 150, "提示", "");
        }

        /// <summary>
        /// 从excel中获得待入库的企业信息
        /// </summary>
        /// <param name="uploadPath"></param>
        /// <returns></returns>
        public List<ImportCompanyInfo> GetCompanyInfoByExcel(string uploadPath, out string msg, out List<ImportCompanyInfo> errList)
        {
            var list = new List<ImportCompanyInfo>();
            ImportCompanyInfo corp = null;

            errList = new List<ImportCompanyInfo>();

            try
            {
                DataTable dt = GetExcelTable(uploadPath);

                if (dt != null)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        corp = new ImportCompanyInfo();
                        corp.CorpName = row[0].ToString().Trim();                        
                        bool isValid = this.CheckOrginazationCode(row[2].ToString());
                        
                        corp.Address = row[4].ToString();
                        corp.Linkman = row[5].ToString();
                        corp.LinkMan1 = row[5].ToString();
                        corp.Tel = row[6].ToString();
                        corp.Tel1 = row[6].ToString();
                        corp.AreaCode = "1401";
                        corp.AreaCode2 = "1401";
                        corp.Type = "15";
                        corp.FlowCode = "0";
                        corp.FlowCode2 = "0";
                        corp.tb_Corp_IsOuter = 0;
                        corp.CertNumber = 0;
                        corp.CorpGuid = Guid.NewGuid();
                        if (!isValid)
                        {
                            corp.Description = "组织机构代码格式不正确";
                            corp.OrginazationCode = row[2].ToString().Trim();
                            errList.Add(corp);
                        }
                        else
                        {
                            corp.OrginazationCode = row[2].ToString().Trim();
                            list.Add(corp);
                        }
                    }
                    msg = "true";
                }
                else
                {
                    msg = "excel中无数据或文件格式有误！";
                }
            }
            catch (Exception e)
            {
                msg = e.Message;
            }

            return list;
        }
        public DataTable GetExcelTable(string uploadPath)
        {
            DataSet ds = new DataSet();
            ////HDR为yes 则第一数据行为列名,为no则自动为列加列名F1  F2  F3
            string Xls_ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + uploadPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
            OleDbConnection Conn = new OleDbConnection(Xls_ConnStr);
            try
            {
                Conn.Open();
                string sql_str = "select * from [Sheet1$]";
                OleDbDataAdapter da = new OleDbDataAdapter(sql_str, Conn);
                da.Fill(ds, "excel_data");
                Conn.Close();
            }
            catch
            {
                if (Conn.State == ConnectionState.Open)
                {
                    Conn.Close();
                }
                return null;
            }
            finally
            {
                Conn.Dispose();
            }

            if (ds == null)
            {
                return null;
            }

            if (ds.Tables.Count < 1)
            {
                return null;
            }

            return ds.Tables[0];
        }

        /// <summary>
        /// 检查组织机构代码是否正确
        /// </summary>
        /// <param name="orginazationCode"></param>
        /// <returns></returns>
        private bool CheckOrginazationCode(string orginazationCode)
        {
            if (string.IsNullOrEmpty(orginazationCode))
                return false;
            Regex reg = new Regex(@"^\d{8}[-][\d|x]$");
            if (reg.IsMatch(orginazationCode.Trim().ToLower()) )
            {
                return true;
            }
            return false;
        }

        protected void RadAjaxManager1_AjaxRequest(object sender, AjaxRequestEventArgs e)
        {
            if (e.Argument == "Rebind")
            {
                ListCorps.MasterTableView.SortExpressions.Clear();
                ListCorps.MasterTableView.GroupByExpressions.Clear();
                ListCorps.Rebind();
            }

        }
        protected void ListCorps_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
        {
            
        }

    }
}